package com.office.automate.dataaccess.impl.leave;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;

import com.office.automate.dataaccess.def.leave.EmployeeLeaveDao;
import com.office.automate.dataaccess.framework.DataAccessSessionFactory;
import com.office.automate.dataaccess.impl.administration.PublicHolidayDaoImplementer;
import com.office.automate.dataaccess.impl.user.EmployeeDaoImplementer;
import com.office.automate.exception.framework.FrontierVisibleException;
import com.office.automate.exception.leave.EmployeeLeaveExceptionKeys;
import com.office.automate.frontier.utility.DataConversionUtility;
import com.office.automate.model.leave.EmployeeLeave;
import com.office.automate.model.leave.LeaveRequest;
import com.office.automate.model.leave.LieuLeaveRequest;
import com.office.automate.model.report.EmployeeLeaveDetailsRequest;
import com.office.automate.statics.ApplicationStatics;

public class EmployeeLeaveDaoImplementer implements EmployeeLeaveDao {
	private SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
	private DataConversionUtility dtUtility = new DataConversionUtility();
	private static SessionFactory sessionFactory = null;
	private Session session = null;

	static {
		sessionFactory = DataAccessSessionFactory.getSessionFactory();
	}

	public EmployeeLeaveDaoImplementer() {
		session = sessionFactory.openSession();		
	}

	@SuppressWarnings("unchecked")
	public List<EmployeeLeave> getCurrentEntitlementBalance(long anEmployeeId) throws FrontierVisibleException {
		List<EmployeeLeave> employeeLeave = null;
		try {
			 String SQL_QUERY ="from EmployeeLeave employeeleave where employeeleave.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", anEmployeeId);
			 employeeLeave = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return employeeLeave;
	}

	public LeaveRequest getLeaveRequestDetails(long aLeaveRequestId) throws FrontierVisibleException {
		LeaveRequest leaveRequest = null;
		try {
			 String SQL_QUERY ="from LeaveRequest leaveRequest where leaveRequest.requestId= :requestId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("requestId", aLeaveRequestId);
			 leaveRequest = (LeaveRequest) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return leaveRequest;
	}

	public EmployeeLeave getCurrentEntitlementBalance(int aFnYear, long anEmployeeId) throws FrontierVisibleException {
		EmployeeLeave employeeLeave = null;
		try {
			 String SQL_QUERY ="from EmployeeLeave employeeleave where employeeleave.currentYear= :fnYear and employeeleave.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("fnYear", aFnYear);
			 queryResult.setParameter("employeeId", anEmployeeId);
			 employeeLeave = (EmployeeLeave) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return employeeLeave;
	}

	public void updateEmployeeLeave(EmployeeLeave anEmployeeLeave) throws FrontierVisibleException {
		try {
			session.beginTransaction();   
			session.update(anEmployeeLeave);
			session.getTransaction().commit();  
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
	}

	public Map<String, Object> addLeaveRequest(LeaveRequest leaveRequest) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			EmployeeLeave employeeLeave = new EmployeeLeaveDaoImplementer().
													getCurrentEntitlementBalance(leaveRequest.getFinancialYear(),
																				 leaveRequest.getEmployeeId());
			String leaveType = leaveRequest.getLeaveType();
			if (leaveType.equals("Annual")) {
				employeeLeave.setAnualLeaveCount(employeeLeave.getAnualLeaveCount() - leaveRequest.getNumberOfDays());
			} else if (leaveType.equals("Casual")) {
				employeeLeave.setCasualLeaveCount(employeeLeave.getCasualLeaveCount() - leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("Medical")) {
				employeeLeave.setMedicalLeaveCount(employeeLeave.getMedicalLeaveCount() - leaveRequest.getNumberOfDays());			
			} else if (leaveType.equals("Lieu")) {
				employeeLeave.setLieuLeaveCount(employeeLeave.getLieuLeaveCount() - leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("MatOrPat")) {
				employeeLeave.setMaternityOrPaternityLeaveCount(employeeLeave.getMaternityOrPaternityLeaveCount() - leaveRequest.getNumberOfDays());				
			}
			Transaction transaction = session.beginTransaction();
			session.save(leaveRequest);
			transaction.commit();
			new EmployeeLeaveDaoImplementer().updateEmployeeLeave(employeeLeave);
			emailRequiredData.put("LeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getEmployeeId()));
			emailRequiredData.put("LeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getApproverEmployeeId()));
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_ADDING_LEAVE_REQUEST, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LeaveRequest> getEmployeeLeaveRequestsList(Map<String, Object> aQueryCriterias, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<LeaveRequest> anEmployeeLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LeaveRequest leaveRequest where leaveRequest.employeeId= :employeeId order by leaveRequest.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLeaveRequestsList;
	}

	@Override
	public int getAvailableEmployeeLeaveRequestsRecordCount(Map<String, Object> aQueryCriterias) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from LeaveRequest leaveRequest where leaveRequest.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		}
		return recordCount;
	}

	@Override
	public Map<String, Object> cancelLeaveRequest(LeaveRequest aLeaveRequest) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LeaveRequest leaveRequest = new EmployeeLeaveDaoImplementer().getLeaveRequestDetails(aLeaveRequest.getRequestId());
			leaveRequest.setStatus(ApplicationStatics.CANCEL);
			session.beginTransaction();   
			session.update(leaveRequest);
			session.getTransaction().commit();

			EmployeeLeave employeeLeave = new EmployeeLeaveDaoImplementer().
					getCurrentEntitlementBalance(leaveRequest.getFinancialYear(),
												 leaveRequest.getEmployeeId());
			String leaveType = leaveRequest.getLeaveType();
			int workingDaysCount = dtUtility.getWorkingDaysCountForGivenDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate()) - 
										new PublicHolidayDaoImplementer().getAvailablePublicHolidayRecordCountForDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate());
			leaveRequest.setNumberOfDays(workingDaysCount);
			if (leaveType.equals("Annual")) {
				employeeLeave.setAnualLeaveCount(employeeLeave.getAnualLeaveCount() + leaveRequest.getNumberOfDays());
			} else if (leaveType.equals("Casual")) {
				employeeLeave.setCasualLeaveCount(employeeLeave.getCasualLeaveCount() + leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("Medical")) {
				employeeLeave.setMedicalLeaveCount(employeeLeave.getMedicalLeaveCount() + leaveRequest.getNumberOfDays());			
			} else if (leaveType.equals("Lieu")) {
				employeeLeave.setLieuLeaveCount(employeeLeave.getLieuLeaveCount() + leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("MatOrPat")) {
				employeeLeave.setMaternityOrPaternityLeaveCount(employeeLeave.getMaternityOrPaternityLeaveCount() + leaveRequest.getNumberOfDays());				
			}
			new EmployeeLeaveDaoImplementer().updateEmployeeLeave(employeeLeave);
			emailRequiredData.put("LeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getEmployeeId()));
			emailRequiredData.put("LeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LeaveRequest", leaveRequest);

		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public int getAvailableEmployeeLeaveApprovalRequestRecordCount(long aCurrentLoginUserId) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from LeaveRequest leaveRequest where leaveRequest.approverEmployeeId= :employeeId and leaveRequest.status= :leaveStatus";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("leaveStatus", ApplicationStatics.PENDING_APPROVAL);
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LeaveRequest> getEmployeeLeaveApprovalRequestsList(long aCurrentLoginUserId, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<LeaveRequest> anEmployeeLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LeaveRequest leaveRequest where leaveRequest.approverEmployeeId= :employeeId and leaveRequest.status= :leaveStatus order by leaveRequest.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("leaveStatus", ApplicationStatics.PENDING_APPROVAL);
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLeaveRequestsList;

	}

	@Override
	public Map<String, Object> approveLeaveRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LeaveRequest leaveRequest = new EmployeeLeaveDaoImplementer().getLeaveRequestDetails(aRequestId);
			leaveRequest.setStatus(ApplicationStatics.APPROVED);
			session.beginTransaction();   
			session.update(leaveRequest);
			session.getTransaction().commit();

			int workingDaysCount = dtUtility.getWorkingDaysCountForGivenDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate()) - 
										new PublicHolidayDaoImplementer().getAvailablePublicHolidayRecordCountForDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate());
			leaveRequest.setNumberOfDays(workingDaysCount);
	
			emailRequiredData.put("LeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getEmployeeId()));
			emailRequiredData.put("LeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LeaveRequest", leaveRequest);

		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;	
	}

	@Override
	public Map<String, Object> rejectLeaveRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LeaveRequest leaveRequest = new EmployeeLeaveDaoImplementer().getLeaveRequestDetails(aRequestId);
			leaveRequest.setStatus(ApplicationStatics.REJECTED);
			session.beginTransaction();   
			session.update(leaveRequest);
			session.getTransaction().commit();

			EmployeeLeave employeeLeave = new EmployeeLeaveDaoImplementer().
					getCurrentEntitlementBalance(leaveRequest.getFinancialYear(),
												 leaveRequest.getEmployeeId());
			String leaveType = leaveRequest.getLeaveType();
			int workingDaysCount = dtUtility.getWorkingDaysCountForGivenDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate()) - 
										new PublicHolidayDaoImplementer().getAvailablePublicHolidayRecordCountForDateRange(leaveRequest.getFromDate(), leaveRequest.getToDate());
			leaveRequest.setNumberOfDays(workingDaysCount);
			if (leaveType.equals("Annual")) {
				employeeLeave.setAnualLeaveCount(employeeLeave.getAnualLeaveCount() + leaveRequest.getNumberOfDays());
			} else if (leaveType.equals("Casual")) {
				employeeLeave.setCasualLeaveCount(employeeLeave.getCasualLeaveCount() + leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("Medical")) {
				employeeLeave.setMedicalLeaveCount(employeeLeave.getMedicalLeaveCount() + leaveRequest.getNumberOfDays());			
			} else if (leaveType.equals("Lieu")) {
				employeeLeave.setLieuLeaveCount(employeeLeave.getLieuLeaveCount() + leaveRequest.getNumberOfDays());				
			} else if (leaveType.equals("MatOrPat")) {
				employeeLeave.setMaternityOrPaternityLeaveCount(employeeLeave.getMaternityOrPaternityLeaveCount() + leaveRequest.getNumberOfDays());				
			}
			new EmployeeLeaveDaoImplementer().updateEmployeeLeave(employeeLeave);
			emailRequiredData.put("LeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getEmployeeId()));
			emailRequiredData.put("LeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(leaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LeaveRequest", leaveRequest);

		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LeaveRequest> getEmployeeLeaveDetails(EmployeeLeaveDetailsRequest anEmployeeLeaveDetailsRequest)
			throws FrontierVisibleException {
		List<LeaveRequest> anEmployeeLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LeaveRequest leaveRequest where leaveRequest.employeeId= :employeeId and (leaveRequest.fromDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD') or leaveRequest.toDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD')) order by leaveRequest.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", anEmployeeLeaveDetailsRequest.getEmployeeId());
			 queryResult.setParameter("FromDate", formatter.format(anEmployeeLeaveDetailsRequest.getFromDate()));
			 queryResult.setParameter("ToDate", formatter.format(anEmployeeLeaveDetailsRequest.getToDate()));
			 anEmployeeLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLeaveRequestsList;
	}

	@Override
	public Map<String, Object> addLieuLeaveRequest(LieuLeaveRequest lieuLeaveRequest) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			Transaction transaction = session.beginTransaction();
			session.save(lieuLeaveRequest);
			transaction.commit();
			emailRequiredData.put("LieuLeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getEmployeeId()));
			emailRequiredData.put("LieuLeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getApproverEmployeeId()));
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_ADDING_LEAVE_REQUEST, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public Map<String, Object> approveLieuLeaveRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LieuLeaveRequest lieuLeaveRequest = new EmployeeLeaveDaoImplementer().getLieuLeaveRequestDetails(aRequestId);
			lieuLeaveRequest.setStatus(ApplicationStatics.APPROVED);
			session.beginTransaction();   
			session.update(lieuLeaveRequest);
			session.getTransaction().commit();
			Calendar calenderDate = Calendar.getInstance();
			calenderDate.setTime(lieuLeaveRequest.getLieuLeaveDate());
			int financialYear=calenderDate.get(Calendar.YEAR);

			EmployeeLeave employeeLeave = new EmployeeLeaveDaoImplementer().
										getCurrentEntitlementBalance(financialYear,
																	 lieuLeaveRequest.getEmployeeId());
			employeeLeave.setLieuLeaveCount(employeeLeave.getLieuLeaveCount() + 1);
			new EmployeeLeaveDaoImplementer().updateEmployeeLeave(employeeLeave);

			emailRequiredData.put("LieuLeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getEmployeeId()));
			emailRequiredData.put("LieuLeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LieuLeaveRequest", lieuLeaveRequest);

		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;	
	}

	@Override
	public Map<String, Object> rejectLieuLeaveRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LieuLeaveRequest lieuLeaveRequest = new EmployeeLeaveDaoImplementer().getLieuLeaveRequestDetails(aRequestId);
			lieuLeaveRequest.setStatus(ApplicationStatics.REJECTED);
			session.beginTransaction();   
			session.update(lieuLeaveRequest);
			session.getTransaction().commit();
			emailRequiredData.put("LieuLeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getEmployeeId()));
			emailRequiredData.put("LieuLeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LieuLeaveRequest", lieuLeaveRequest);
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public Map<String, Object> cancelLieuLeaveRequest(long aRequestId) throws FrontierVisibleException {
		Map<String, Object> emailRequiredData = new HashMap<String, Object>();
		try {
			LieuLeaveRequest lieuLeaveRequest = new EmployeeLeaveDaoImplementer().getLieuLeaveRequestDetails(aRequestId);
			lieuLeaveRequest.setStatus(ApplicationStatics.CANCEL);
			session.beginTransaction();   
			session.update(lieuLeaveRequest);
			session.getTransaction().commit();
			emailRequiredData.put("LieuLeaveRequesterDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getEmployeeId()));
			emailRequiredData.put("LieuLeaveApproverDetails", new EmployeeDaoImplementer().getEmployeeDetails(lieuLeaveRequest.getApproverEmployeeId()));
			emailRequiredData.put("LieuLeaveRequest", lieuLeaveRequest);
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_UPDATING_LEAVE_DETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return emailRequiredData;
	}

	@Override
	public LieuLeaveRequest getLieuLeaveRequestDetails(long aLieuLeaveRequestId) throws FrontierVisibleException {
		LieuLeaveRequest lieuLeaveRequest = null;
		try {
			 String SQL_QUERY ="from LieuLeaveRequest lieuLeaveRequest where lieuLeaveRequest.requestID= :requestId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("requestId", aLieuLeaveRequestId);
			 lieuLeaveRequest = (LieuLeaveRequest) queryResult.uniqueResult();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return lieuLeaveRequest;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LieuLeaveRequest> getEmployeeLieuLeaveRequestsList(Map<String, Object> aQueryCriterias, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException{
		List<LieuLeaveRequest> anEmployeeLieuLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LieuLeaveRequest lieuLeaveRequest where lieuLeaveRequest.employeeId= :employeeId order by lieuLeaveRequest.requestID desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeLieuLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLieuLeaveRequestsList;
	}

	@Override
	public int getAvailableEmployeeLieuLeaveRequestsRecordCount(Map<String, Object> aQueryCriterias) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from LieuLeaveRequest lieuLeaveRequest where lieuLeaveRequest.employeeId= :employeeId";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aQueryCriterias.get("EmployeeId"));
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LieuLeaveRequest> getEmployeeLieuLeaveApprovalRequestsList(long aCurrentLoginUserId, int aStartingIndex, int aNumberOfRecords) throws FrontierVisibleException {
		List<LieuLeaveRequest> anEmployeeLieuLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LieuLeaveRequest lieuLeaveRequest where lieuLeaveRequest.approverEmployeeId= :employeeId and lieuLeaveRequest.status= :Status order by lieuLeaveRequest.requestID desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("Status", ApplicationStatics.PENDING_APPROVAL);
			 queryResult.setFirstResult(aStartingIndex);
			 queryResult.setMaxResults(aNumberOfRecords);
			 anEmployeeLieuLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLieuLeaveRequestsList;
	}

	@Override
	public int getAvailableEmployeeLieuLeaveApprovalRequestRecordCount(long aCurrentLoginUserId) throws FrontierVisibleException {
		int recordCount = 0;
		try {
			 String SQL_QUERY ="select count(*) from LieuLeaveRequest lieuLeaveRequest where lieuLeaveRequest.approverEmployeeId= :employeeId and lieuLeaveRequest.status= :Status";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("employeeId", aCurrentLoginUserId);
			 queryResult.setParameter("Status", ApplicationStatics.PENDING_APPROVAL);
			 recordCount = ((Long) queryResult.list().get(0)).intValue();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		}
		return recordCount;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<LeaveRequest> getEmployeeLeaveSummaryDetails(EmployeeLeaveDetailsRequest anEmployeeLeaveDetailsRequest) throws FrontierVisibleException {
		List<LeaveRequest> anEmployeeLeaveRequestsList = null;
		try {
			 String SQL_QUERY ="from LeaveRequest leaveRequest where leaveRequest.fromDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD') or leaveRequest.toDate between to_date(:FromDate,'YYYY-MM-DD') and to_date(:ToDate,'YYYY-MM-DD') order by leaveRequest.requestId desc";
			 Query queryResult = session.createQuery(SQL_QUERY);
			 queryResult.setParameter("FromDate", formatter.format(anEmployeeLeaveDetailsRequest.getFromDate()));
			 queryResult.setParameter("ToDate", formatter.format(anEmployeeLeaveDetailsRequest.getToDate()));
			 anEmployeeLeaveRequestsList = queryResult.list();
		} catch(Exception e) {
			throw new FrontierVisibleException(EmployeeLeaveExceptionKeys.ERROR_OCCORRED_DURING_RETRIEVAL_LEAVEDETAILS, e);
		} finally {
			session.flush();
			session.close();
		}
		return anEmployeeLeaveRequestsList;
	}
}
